Grokking SQL for Tech Interviews
Rate this course
Ask Author
Contribute
Back to course home

0% completed

Vote For New Content
Introduction
Course Overview
What is RDBMS?
What is SQL?
A Quick Review of SQL
Read data from table
WHERE Clause
Operators
Handle NULLs in SQL
DISTINCT Clause
ORDER BY Clause
LIMIT and OFFSET
GROUP BY
HAVING
Aggregate Functions
Order of Execution
Order of SQL Query Execution
SQL Functions
Number Functions
String Functions
Date Functions
Nested Query
Types of Subqueries
Introduction
INNER JOIN
LEFT OUTER JOIN
RIGHT OUTER JOIN
FULL OUTER JOIN
SELF JOIN
What are SET operations?
UNION
INTERSECT
EXCEPT
Keys in SQL
CREATE
ALTER
DROP
TRUNCATE
Indexes
INSERT
UPDATE
DELETE
Backup Database
Restore Database
1. Major Cities
2. Not boring movies
3. Triangle Judgement
4. Biggest Single Number
5. Product Sales Analysis I
6. Product Sales Analysis II
7. Game Play Analysis I
8. Game Play Analysis II
9. Queries Quality and Percentage
10. Average Selling Price
11. Weather Type in Each Country
12. Students and Examinations
13. Ads Performance
14. Group Sold Products By The Date
15. Employee Email Verification
16. Sellers With No Sales
17. Average Time of Process
18. Find Followers Count
19. Ad-Free Sessions
20. Bikes Last Time Used
21. Daily User Engagement Levels
22. Employee Attendance Record
23. Library Book Loans
24. Customer Purchase Summary
25. Long Comments (Easy)
26. Unused Accounts
27. Patient Appointment No-Shows
28. Books and Authors
29. High School Attendance
30. Employee Absences
31. Green Product Identification (Easy)
32. Employee Department Exclusion (Easy)
33. Sales Person (Easy)
34. Employee Task Tracking (Easy)
1. Winning Candidate
2. Second Degree Follower
3. 2nd Highest Salary
4. Game Play Analysis III
5. Game Play Analysis IV
6. Active Businesses
7. Product Price on a Specific Date
8. Monthly Transactions II
9. Team Scores in Football Tournament
10. Page Recommendations
11. Running Total for Different Genders
12. Evaluate Boolean Expression
13. Active Users
14. Find Interview Candidates
15. Suspicious Bank Accounts
16. Order Fulfillment Rate (Medium)
17. Last Person to Fit in the Elevator (Medium)
18. Retail Expansion Analysis (Medium)
19. Employee Collaboration Networks (Medium)
20. Second Fastest Time in Races (Medium)
1. Trips and Users
2. Human Traffic of Stadium
3. Students Report By Geography
4. Game Play Analysis V
5. Number of Transactions per Visit
6. Find the Quiet Students in All Exams
7. First and Last Call On the Same Day
8. Build the Equation
9. Longest Winning Streak
10. Dynamic Pivoting of a Table
11. Dynamic Unpivoting of a Table
12. Generate the Invoice
13. Popularity Percentage
14. Viewers Turned Streamers
15. Status of Flight Tickets
1. High Performing Employees (Easy)
2. Student Course Averages (Easy)
3. Library Book Checkout Percentage (Easy)
4. Employee Performance and Improvement Rate (Easy)
5. Find Popular Posts (Easy)
6. Main Office Assignment for Each Employee (Easy)
7. Rectangle Validation (Easy)
8. Library Book Checkouts by Date (Easy)
9. Same-Day Appointment Fulfillment (Medium)
10. Departments with High Earning Employees (Medium)
11. Students Who Attended All Courses (Medium)
12. Library Book Checkouts First Year (Medium)
13. Repeated Values in Sequence (Medium)
14. School Top Achievers by Subject (Hard)
String Functions

Just as numeric functions simplify numerical operations, SQL's string functions are indispensable tools for manipulating and analyzing text data. These functions allow you to perform a wide range of operations, from simple concatenations to complex pattern matching, enabling you to derive meaningful insights from textual information.

Key String Functions in SQL

Let's explore some of the most essential string functions in SQL, complete with descriptions and practical examples to illustrate their usage.

1. CONCAT()

The CONCAT() function combines two or more strings into a single unit. This is particularly useful for merging columns or adding static text to query results.

Example:

SELECT CONCAT('Hello', ' ', 'World') AS Greeting; -- Result: Hello World

2. SUBSTRING()

The SUBSTRING() function extracts a portion of a string based on specified starting position and length. It's ideal for parsing data or isolating specific parts of a string.

Example:

SELECT SUBSTRING('SQL Functions', 1, 3) AS SubStr; -- Result: SQL

3. LENGTH()

The LENGTH() function returns the length of a string in characters. It's useful for validating data, such as ensuring inputs meet required lengths.

Example:

SELECT LENGTH('Data') AS Length; -- Result: 4

4. UPPER() and LOWER()

  • UPPER(): Converts a string to uppercase.
  • LOWER(): Converts a string to lowercase.

These functions are essential for standardizing text data, especially when performing case-insensitive comparisons.

Example:

SELECT UPPER('hello') AS UpperCase, LOWER('WORLD') AS LowerCase; -- Results: HELLO, world

5. REPLACE()

The REPLACE() function substitutes all occurrences of a specified substring within a string with another substring. It's useful for correcting or modifying data entries.

Example:

SELECT REPLACE('I love SQL', 'SQL', 'coding') AS NewString; -- Result: I love coding

6. TRIM()

The TRIM() function removes leading and trailing spaces from a string. This is particularly useful for cleaning up data inputs and ensuring consistency.

Example:

SELECT TRIM(' SQL ') AS TrimmedString; -- Result: SQL

SQL String Functions

To provide a clearer overview, here's a detailed table outlining various string functions in SQL, complete with descriptions and example queries:

Sr      FunctionDescriptionQueryResult      
1CONCAT()Combines two or more strings into a single string.SELECT CONCAT('Hello', ' ', 'World') AS Greeting;Hello World
2SUBSTRING()Extracts a substring from a string starting at a specified position and length.SELECT SUBSTRING('SQL Functions', 1, 3) AS SubStr;SQL
3LENGTH()Returns the length of a string in characters.SELECT LENGTH('Data') AS Length;4
4UPPER()Converts a string to uppercase letters.SELECT UPPER('hello') AS UpperCase;HELLO
5LOWER()Converts a string to lowercase letters.SELECT LOWER('WORLD') AS LowerCase;world
6REPLACE()Replaces all occurrences of a specified substring within a string with another substring.SELECT REPLACE('I love SQL', 'SQL', 'coding') AS NewString;I love coding
7TRIM()Removes leading and trailing spaces from a string.SELECT TRIM(' SQL ') AS TrimmedString;SQL
8CHAR_LENGTH()Returns the number of characters in a string.SELECT CHAR_LENGTH('MySQL') AS CharLength;5
9LEFT()Returns the left part of a string with the specified number of characters.SELECT LEFT('Database', 3) AS LeftPart;Dat
10RIGHT()Returns the right part of a string with the specified number of characters.SELECT RIGHT('MySQL', 3) AS RightPart;SQL
11REPEAT()Repeats a string a specified number of times.SELECT REPEAT('La', 3) AS RepeatedString;LaLaLa
12POSITION()Returns the position of the first occurrence of a substring within a string.SELECT POSITION('abc' IN 'abcdef') AS Position;1
13LOCATE()Returns the position of the first occurrence of a substring within a string. Similar to POSITION().SELECT LOCATE('World', 'Hello World') AS LocatePos;7
14INSTR()Returns the position of the first occurrence of a substring within a string. Similar to POSITION().SELECT INSTR('Hello World', 'World') AS InstrPos;7
15LPAD()Pads the left side of a string with a specified character to a certain length.SELECT LPAD('SQL', 5, '*') AS PaddedLeft;**SQL
16RPAD()Pads the right side of a string with a specified character to a certain length.SELECT RPAD('SQL', 5, '*') AS PaddedRight;SQL**
17SUBSTRING_INDEX()Returns a substring from a string before a specified number of occurrences of a delimiter.SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2) AS SubStrIdx;www.mysql
18CONCAT_WS()Concatenates strings with a separator.SELECT CONCAT_WS('-', '2024', '01', '01') AS DateFormatted;2024-01-01

Learning with Interactive Exercises

To solidify understanding, incorporate interactive exercises and real-world projects where students can apply these string functions. For example:

  • Exercise 1: Create a query that concatenates a user's first and last name with a space in between.

    SELECT CONCAT(first_name, ' ', last_name) AS FullName FROM users;
  • Exercise 2: Develop a report that extracts the domain from each user's email address.

    SELECT SUBSTRING_INDEX(email, '@', -1) AS Domain FROM users;
Number Functions
Date Functions
Mark as Completed

On this page

Key String Functions in SQL

  1. CONCAT()
  1. SUBSTRING()
  1. LENGTH()
  1. UPPER() and LOWER()
  1. REPLACE()
  1. TRIM()

SQL String Functions

Learning with Interactive Exercises